﻿using Lamex.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lamex.SIESystemManager.Calculate
{
    public class C_BOM_Routing
    {
        /// <summary>
        /// 获取工艺路线
        /// </summary>
        /// <param name="PRODUCT_DI">产品分配码</param>
        /// <param name="MATERIAL_GROUP">物料组</param>
        /// <returns></returns>
        public String GetRouting(string PRODUCT_DI,DataTable MATERIAL_GROUP)
        {
            string Routing_List = "";
            if (MATERIAL_GROUP != null)
            {
                switch (PRODUCT_DI)
                {
                    case "GD":

                        if (MATERIAL_GROUP.Select("MATERIAL_GROUP='34'").Length > 0 || MATERIAL_GROUP.Select("MATERIAL_GROUP='53'").Length > 0)
                            Routing_List = "D44-D51";
                        else
                            Routing_List = "D44";

                        break;
                    case "GZ":

                        if (MATERIAL_GROUP.Select("MATERIAL_GROUP='34'").Length > 0 || MATERIAL_GROUP.Select("MATERIAL_GROUP='53'").Length > 0)
                            Routing_List = "D47-D51";
                        else
                            Routing_List = "D47";

                        break;
                    case "PJ":
                        Routing_List = "D54";

                        break;
                    default:

                        Routing_List = "D51";

                        break;
                }
            }
            return Routing_List;
        }
        /// <summary>
        /// 获取动作名称
        /// </summary>
        /// <param name="Routing_List">工艺路线</param>
        /// <returns></returns>
        public List<string> GetOperation(string Routing_List,string Effective_time)
        {
            List<string> list = new List<string>();
            string[] routing = Routing_List.Split('-');
            foreach (var item in routing)
            {
                string sql = @"SELECT 
      [OPERATIONAL_NAME]
      ,[TYPE]
      ,[UNIT]
      ,[UNIT_WORKHOURS]
      ,[ATTACH_WORKHOURS]    
      ,[OPTION_VALUES]
      ,[WORKSTATION_LIST]
      ,[EFFECTIVE_TS]
      ,[END_TS]
  FROM [LamexMESManager].[dbo].[B_IE_Operational_Definition]
  where WORKSTATION_LIST like '%" + item + "%'  and EFFECTIVE_TS<='" + Effective_time + " 23:59:59' and END_TS>='" + Effective_time + " 00:00:00' "; //增加生效时间监控"
                DataTable dt = new DataTable();
                dt = BaseBuildSql.GetDataTableBySql(sql);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    list.Add(dt.Rows[i]["OPERATIONAL_NAME"].ToString());
                }
            }
            return list;
        }
        /// <summary>
        /// 获取动作特征值
        /// </summary>
        /// <param name="list">动作名称</param>
        /// <param name="Routing_List">工艺路线</param>
        /// <param name="dtContrast">主数据与BOM表交集</param>
        /// <param name="dtBOM">BOM表</param>
        /// <param name="Operation">动作定义表</param>
        /// <param name="dtBom_NECESSARY">物料适用表</param>
        /// <returns></returns>
        public DataTable GetOPeration_Values(string Flag,string BOM, List<string> list, string Routing_List, DataTable dtContrast, DataTable dtBOM, DataTable Operation,DataTable dtBom_NECESSARY)
        {
            //DataTable dt = new DataTable();
            //dt.Columns.Add("WorkStation");
            //dt.Columns.Add("Operation_Name");
            //dt.Columns.Add("values");
            //  ,'" + Lamex.Common.Loginer.CurrentUser.Account + @"'
            string[] routing = Routing_List.Split('-');//工艺路线
            DataRow dr = Operation.NewRow();
            dr["DRAWING_NUMBER"] = BOM;
            dr["CREATE_USER"] = Lamex.Common.Loginer.CurrentUser.Account;
            dr["CREATE_DATE"] = DateTime.Now;
            if (Flag == "BOM_SJ")
                Flag = "4";
            else
                Flag = "3";
            dr["FLAG"] = Flag;
            foreach (var item in routing)
            {
                #region D44特征值
                if (item == "D44")
                {
                    string Type = "";
                    double QTY = 0;
                   
                    foreach (var item1 in list)//动作名称
                    {
                        switch (item1)
                        {
                            case "柜桶类型":
                                if (DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='柜桶类型'").CopyToDataTable()).Rows.Count > 0)
                                //if (dtContrast.Rows.Count > 0)
                                    Type = "胶抽";
                                else
                                    Type = "木抽";
                                break;
                            case "装抽屉数量":
                                QTY = Convert.ToDouble(dtBOM.Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "' and MATERIAL_GROUP='23'").ToString().Length > 0 ?
                                    dtBOM.Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "' and MATERIAL_GROUP='23'") : "0");
                                break;
                        }

                    }
                    dr["D44_Barrel_Type"] = Type;
                    dr["D44_Drawers_Qty"] = QTY;
                    //Operation.Rows.Add(dr);
                }
                #endregion
                #region D47 特征值
                if (item == "D47")
                {
                    double QTY = 0, QTY_500 = 0, QTY_1300 = 0, QTY_1301 = 0, QTY_JGQ = 0, QTY_LM = 0, QTY_TM = 0, QTY_LS = 0, QTY_ZL = 0, QTY_10 = 0, QTY_30 = 0, QTY_50 = 0;
                
                    foreach (var item1 in list)//动作名称
                    {
                        switch (item1)
                        {
                            case "装抽屉数量":
                                QTY = Convert.ToDouble(dtBOM.Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "' and MATERIAL_GROUP='23'").ToString().Length > 0 ?
                                    dtBOM.Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "' and MATERIAL_GROUP='23' ") : "0");
                                break;
                            case "装板材500":
                                QTY_500 = Convert.ToDouble(dtBOM.Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "' and Width<500 and Length<500 and Width>0 and Length>0 ").ToString().Length > 0 ?
                                    dtBOM.Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "' and Width>0 and Length>0 and Width<500 and Length<500") : "0");
                                break;
                            case "装板材501-1300":
                                QTY_1300 = Convert.ToDouble(dtBOM.Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "' and Width<1300 and Length<1300 and (width>500 or Length>500)").ToString().Length > 0 ?
                                    dtBOM.Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "' and  Width<1300 and Length<1300 and (width>500 or Length>500)") : "0");
                                break;
                            case "装板材1301":
                                QTY_1301 = Convert.ToDouble(dtBOM.Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "' and (Width>=1300 or Length>=1300)").ToString().Length > 0 ?
                                    dtBOM.Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "'  and (Width>=1300 or Length>=1300)") : "0");
                                break;
                            case "拧金刚针圈":
                                QTY_JGQ = Convert.ToDouble(DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='拧金刚针圈'").CopyToDataTable()).Rows.Count > 0 ?
                                    (DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='拧金刚针圈'").CopyToDataTable()).Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "'").ToString().Length > 0 ?
                                    DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='拧金刚针圈'").CopyToDataTable()).Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "'") : "0") : "0");
                                break;
                            case "装拉门配件":
                                QTY_LM = Convert.ToDouble(DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='装拉门配件'").CopyToDataTable()).Rows.Count > 0 ?
                                    (DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='装拉门配件'").CopyToDataTable()).Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "'").ToString().Length > 0 ?
                                    DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='装拉门配件'").CopyToDataTable()).Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "'") : "0") : "0");
                                break;
                            case "装趟门配件":
                                QTY_TM = Convert.ToDouble(DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='装趟门配件'").CopyToDataTable()).Rows.Count > 0 ?
                                    (DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='装趟门配件'").CopyToDataTable()).Compute("sum(SINGLE_QTY)/2", "MATERIAL_NO='" + BOM + "'").ToString().Length > 0 ?
                                    DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='装趟门配件'").CopyToDataTable()).Compute("sum(SINGLE_QTY)/2", "MATERIAL_NO='" + BOM + "'") : "0") : "0");
                                break;
                            case "锁螺丝数量":
                                QTY_LS = Convert.ToDouble(dtBOM.Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "' and MATERIAL_GROUP='46'").ToString().Length > 0 ?
                                    dtBOM.Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "' and MATERIAL_GROUP='46'") : "0");
                                break;
                            case "配件种类":
                                QTY_ZL = Convert.ToDouble(dtBOM.Compute("count(MATERIAL_NO)", "MATERIAL_NO='" + BOM + "'").ToString().Length > 0 ?
                                    dtBOM.Compute("count(MATERIAL_NO)", "MATERIAL_NO='" + BOM + "'") : "0");
                                break;
                            case "装特别配件10":
                                QTY_10 = Convert.ToDouble(DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='装特别配件10'").CopyToDataTable()).Rows.Count > 0 ?
                                    (DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='装特别配件10'").CopyToDataTable()).Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "'").ToString().Length > 0 ?
                                     DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='装特别配件10'").CopyToDataTable()).Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "'") : "0") : "0");
                                break;
                            case "装特别配件30":
                                QTY_30 = Convert.ToDouble(DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='装特别配件30'").CopyToDataTable()).Rows.Count > 0 ?
                                    (DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='装特别配件30'").CopyToDataTable()).Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "'").ToString().Length > 0 ?
                                     DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='装特别配件30'").CopyToDataTable()).Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "'") : "0") : "0");
                                break;
                            case "装特别配件50":
                                QTY_50 = Convert.ToDouble(DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='装特别配件50'").CopyToDataTable()).Rows.Count > 0 ?
                                    (DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='装特别配件50'").CopyToDataTable()).Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "'").ToString().Length > 0 ?
                                     DataTableContrast(dtContrast, dtBom_NECESSARY.Select("Operation_Name='装特别配件50'").CopyToDataTable()).Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "'") : "0") : "0");
                                break;
                        }

                    }
                    dr["D47_Drawers_Qty"] = QTY;
                    dr["D47_Sheet_Matal_500"] = QTY_500;
                    dr["D47_Sheet_Matal_501_1300"] = QTY_1300;
                    dr["D47_Sheet_Matal_1301"] = QTY_1301;
                    dr["D47_Pulling_Door_Fittings"] = QTY_LM;
                    dr["D47_Install_Door_Fittings"] = QTY_TM;
                    dr["D47_Twisting_Diamond_Needle_Rings"] = QTY_JGQ;
                    dr["D47_Lock_Screws_Qty"] = QTY_LS;
                    dr["D47_Kind_of_Kitting"] = QTY_ZL;
                    dr["D47_Special_Fittings_10"] = QTY_10;
                    dr["D47_Special_Fittings_30"] = QTY_30;
                    dr["D47_Special_Fittings_50"] = QTY_50;
                   // Operation.Rows.Add(dr);
                }
                #endregion
                #region D51特征值
                if (item == "D51")
                {
                    double QTY_PET = 0, QTY_PEJ = 0, QTY_ZXBG = 0, QTY_ZXBJ = 0, QTY = 0;
                    foreach (var item1 in list)//动作名称
                    {
                        switch (item1)
                        {
                            case "PE包装柜桶":
                                if ((Routing_List.Contains("D51") && dtBOM.Select("MATERIAL_NO='" + BOM + "' and COMPONENT='1990054960'").Length > 0) &&
                                    (Routing_List.Contains("D44") || Routing_List.Contains("D47")))
                                    QTY_PET = 1;
                                break;
                            case "PE包装板件":
                                if ((Routing_List.Contains("D51") && dtBOM.Select("MATERIAL_NO='" + BOM + "' and COMPONENT='1990054960'").Length > 0) &&
                                   (!Routing_List.Contains("D44") && !Routing_List.Contains("D47")))
                                    QTY_PEJ = 1;
                                break;
                            case "纸箱包装柜子":
                                if ((Routing_List.Contains("D51") && dtBOM.Select("MATERIAL_NO='" + BOM + "' and COMPONENT='1990054960'").Length <= 0) &&
                                   (Routing_List.Contains("D44") || Routing_List.Contains("D47")))
                                    QTY_ZXBG = 1;
                                break;
                            case "纸箱包装板件":
                                if ((Routing_List.Contains("D51") && dtBOM.Select("MATERIAL_NO='" + BOM + "' and COMPONENT='1990054960'").Length <= 0) &&
                                    (!Routing_List.Contains("D44") && !Routing_List.Contains("D47")))
                                    QTY_ZXBJ = 1;
                                break;
                            case "板件数量":
                                QTY = Convert.ToDouble(dtBOM.Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "' and Width>0 and Length>0 ").ToString().Length > 0 ?
                                    dtBOM.Compute("sum(SINGLE_QTY)", "MATERIAL_NO='" + BOM + "' and Width>0 and Length>0 ") : "0");
                                break;
                        }

                    }
                    dr["D51_PEFilmPackaging"] = QTY_PET;
                    dr["D51_CartonPackaging"] = QTY_PEJ;
                    dr["D51_PaperPackaging"] = QTY_ZXBG;
                    dr["D51_BoardPackaging"] = QTY_ZXBJ;
                    dr["D51_Board_Qty"] = QTY;
                    //Operation.Rows.Add(dr);
                }
                #endregion
                #region D54特征值
                if (item == "D54")
                {
                    double QTY = 0;
                    foreach (var item1 in list)//动作名称
                    {
                        switch (item1)
                        {
                            case "配件种类":
                                QTY = Convert.ToDouble(dtBOM.Compute("count(MATERIAL_NO)", "MATERIAL_NO='" + BOM + "'").ToString().Length > 0 ?
                                    dtBOM.Compute("count(MATERIAL_NO)", "MATERIAL_NO='" + BOM + "'") : "0");
                                break;
                        }
                    }
                    dr["D54_Kind_of_Kitting"] = QTY;
                   
                }
                #endregion
            }
            Operation.Rows.Add(dr);
            return Operation;
        }
        /// <summary>
        /// table交集
        /// </summary>
        /// <param name="table1"></param>
        /// <param name="table2"></param>
        /// <returns></returns>
        public DataTable DataTableContrast(DataTable table1, DataTable table2)
        {
            DataTable dt = new DataTable();
            if (table1 != null && table2 != null)
            {
                var query = (from c in table1.Rows.Cast<DataRow>()

                             join d in table2.Rows.Cast<DataRow>() on c["COMPONENT"] equals d["Material_No"]

                             select c).ToList();
                if (query.Count > 0)
                {
                    dt = query.CopyToDataTable();
                }
            }
            return dt;

        }
        /// <summary>
        /// 创建临时表
        /// </summary>
        /// <returns></returns>
        public DataTable GetTable_Temp()
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[] {   
         new DataColumn("DRAWING_NUMBER",typeof(string)),  
         new DataColumn("CREATE_USER",typeof(string)),  
         new DataColumn("CREATE_DATE",typeof(DateTime)), 
         new DataColumn("FLAG",typeof(string)),     
         new DataColumn("D44_Barrel_Type",typeof(string)), 
         new DataColumn("D44_Drawers_Qty",typeof(double)), 
         new DataColumn("D47_Drawers_Qty",typeof(double)), 
         new DataColumn("D47_Sheet_Matal_500",typeof(double)), 
         new DataColumn("D47_Sheet_Matal_501_1300",typeof(double)),
         new DataColumn("D47_Sheet_Matal_1301",typeof(double)), 
         new DataColumn("D47_Pulling_Door_Fittings",typeof(double)),
         new DataColumn("D47_Install_Door_Fittings",typeof(double)), 
         new DataColumn("D47_Twisting_Diamond_Needle_Rings",typeof(double)),
         new DataColumn("D47_Lock_Screws_Qty",typeof(double)), 
         new DataColumn("D47_Kind_of_Kitting",typeof(double)), 
         new DataColumn("D47_Special_Fittings_10",typeof(double)), 
         new DataColumn("D47_Special_Fittings_30",typeof(double)),
         new DataColumn("D47_Special_Fittings_50",typeof(double)), 
         new DataColumn("D51_PEFilmPackaging",typeof(double)),
         new DataColumn("D51_CartonPackaging",typeof(double)), 
         new DataColumn("D51_PaperPackaging",typeof(double)),
         new DataColumn("D51_BoardPackaging",typeof(double)), 
         new DataColumn("D51_Board_Qty",typeof(double)),       
         new DataColumn("D54_Kind_of_Kitting",typeof(double))});
            return dt;
        }
        public DataTable DataTableContrast_TIME(DataTable table1, DataTable table2)
        {
            DataTable dt = new DataTable();
            if (table1 != null && table2 != null && table1.Rows.Count > 0)
            {
                var query = (from c in table1.Rows.Cast<DataRow>()

                             join d in table2.Rows.Cast<DataRow>() on c["PartNumber"] equals d["PartNumber"]

                             select c).ToList();
                if (query.Count > 0)
                {
                    dt = query.CopyToDataTable();
                }
            }
            return dt;

        }
       
    }
}
